This question comes up time to time. SQL Compare compares two database schemata, and in order to do so it needs to read the system tables and system views. On the other hand, some organizational policies can be rather restrictive about who and with what permissions are allowed to connect to a production system.

The more complex databases make use of object owners to organise the database objects. Objects like tables, views, user defined types can be owned by individual users/schemas. Under SQL Server 2000, in order to read all the schema information, you do need to be dbo, otherwise you will not be able to see some of the object definitions, or you will not be able to learn about the existence of certain database objects.

Under SQL Server 2005 however there is a permission (VIEW DEFINITION) to control access to viewing object definitions. To add this permission to user UserA in a particular database run:

GRANT VIEW DEFINITION TO UserA;

You can also grant this permission at server level by executing:

GRANT VIEW ANY DEFINITION UserA;

The nice thing about this is that with the help of the above permission you are not granting permission to modify the database schema, so you can allow people to use SQL Compare to compare and monitor schema changes without allowing them to modify the databases in question. You can still generate a synchronization script, but that you can later execute as a different user.

Andras